package exttable.testversioning;

import dbutils.DbUtils;
import dbutils.StoredQueries;
import dbutils.StringQueries;
import exttable.testversioning.tablemodel.TestVersioningModel;
import exttable.view.*;
import utils.Localisation;

import javax.swing.*;
import javax.swing.table.TableModel;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Types;
import java.util.Vector;

/**
 * Created by IntelliJ IDEA.
 * User: Admin
 * Date: Jun 16, 2010
 * Time: 10:52:16 AM
 * To change this template use File | Settings | File Templates.
 */
public class TestVersioningDBUtils implements IDBUtils {

    public static final int ANALYSIS_COLUMN = 0;
    public static final int PRESENT_COLUMN = 1;
    public static final int VERSION_COLUMN = 2;

    public Vector<ViewPhases> getPhases() {
        Vector<ViewPhases> toReturn = new Vector<ViewPhases>();
        ViewPhases empty = new ViewPhases();
        toReturn.add(empty);
        try {
            Connection c = DbUtils.getDBConnectionGeneral();
            PreparedStatement ps = c.prepareStatement(StringQueries.PHASE_RETRIEVE);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                ViewPhases vu = new ViewPhases();
                vu.setPhaseId(rs.getString("phase_id"));
                vu.setNameEn(rs.getString("name_en"));
                vu.setNameRu(rs.getString("name_ru"));
                vu.setNameKz(rs.getString("name_kk"));
                toReturn.add(vu);
            }
            c.close();
            return toReturn;
        }
        catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    public boolean saveAnalysisInPhases(JTable unitTable, ViewPhases vp) {
        Connection c;
        try {
            c = DbUtils.getDBConnectionGeneral();
            PreparedStatement ps = c.prepareStatement(StringQueries.TEST_VERSION_INSERT);

            for (int i = 0; i < unitTable.getModel().getRowCount(); i++) {
                String analysisVersionKey = getAnalysisVersionKey(((ViewAnalysis) unitTable.getValueAt(i, ANALYSIS_COLUMN)).getAnalysisId()
                        , ((ViewVersion) unitTable.getValueAt(i, VERSION_COLUMN)).getVersionId());
                ps.setString(1, analysisVersionKey);
                ps.setString(2, vp.getPhaseId());
                ps.addBatch();
            }
            int[] i = ps.executeBatch();
            c.close();
            return i.length > 0;
        }
        catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }

    private String getAnalysisVersionKey(int analysisID, String version) throws Exception {
        String toReturn = "";
        Connection c;
        try {
            c = DbUtils.getDBConnectionGeneral();
            PreparedStatement ps = c.prepareStatement(StringQueries.ANALYSIS_VERSION_KEY);
            ps.setInt(1, analysisID);
            ps.setString(2, version);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                toReturn = rs.getString("analysis_version_key");
            }
            return toReturn;
        }
        catch (Exception e) {
            e.printStackTrace();
            throw new Exception("DB ex erro");
        }
    }

    public boolean processAnalysisInPhases(JTable unitTable, ViewPhases phase) {
        return checkFilledRows(unitTable.getModel()) && gatherData(unitTable, phase);
    }

    private boolean checkFilledRows(TableModel model) {
        int counter = 0;
        for (int i = 0; i < model.getRowCount(); i++) {
            if ((Boolean) model.getValueAt(i, PRESENT_COLUMN)) {
                counter++;
                ViewVersion vv = (ViewVersion) model.getValueAt(i, VERSION_COLUMN);
                if ((Boolean) model.getValueAt(i, PRESENT_COLUMN) && vv.getVersionId().equals("0")) {
                    JOptionPane.showMessageDialog(null, Localisation.getMessage("error.emptyfileds"));
                    return false;
                }
            }
        }
        if (counter == 0) {
            JOptionPane.showMessageDialog(null, Localisation.getMessage("error.analysisnotselected"));
        }
        return counter > 0;
    }

    private boolean gatherData(JTable unitTable, ViewPhases phase) {
        StringBuilder analysisId = new StringBuilder();
        StringBuilder versions = new StringBuilder();

        Vector<ViewAnalysisVersionPhase> result = new Vector<ViewAnalysisVersionPhase>();
        for (int i = 0; i < unitTable.getModel().getRowCount(); i++) {
            if ((Boolean) unitTable.getModel().getValueAt(i, PRESENT_COLUMN)) {
                ViewAnalysisVersionPhase vvp = new ViewAnalysisVersionPhase();
                ViewAnalysis va = (ViewAnalysis) unitTable.getModel().getValueAt(i, ANALYSIS_COLUMN);
                vvp.setAnalysis(va);
                analysisId.append(va.getAnalysisId());
                analysisId.append(", ");
                vvp.setPresent(true);
                ViewVersion vv = (ViewVersion) unitTable.getModel().getValueAt(i, VERSION_COLUMN);
                vvp.setVersion(vv);
                versions.append(vv.getVersionId());
                versions.append(", ");
                result.add(vvp);
            }
        }
        if (checkExistingAnalysisInPhase(analysisId, phase)) {
            unitTable.setModel(new TestVersioningModel(result));
            return true;
        } else {
            return false;
        }

    }

    private boolean checkExistingAnalysisInPhase(StringBuilder analysis, ViewPhases phase) {
        boolean toReturn = true;
        StringBuilder errorMessage = new StringBuilder();

        Connection c;
        try {
            c = DbUtils.getDBConnectionGeneral();
            PreparedStatement ps = c.prepareStatement(buildQuery(analysis, phase.getPhaseId()));
            ResultSet rs = ps.executeQuery();
            errorMessage.append(Localisation.getMessage("message.analysis"));
            errorMessage.append("\n");
            while (rs.next()) {
                toReturn = false;
                errorMessage.append(rs.getString("dial.name_ru") + ": ");
                errorMessage.append(rs.getString("dp.name_ru"));
                errorMessage.append("\n");
            }
            if (!toReturn) {
                errorMessage.append(Localisation.getMessage("message.presentindb"));
                JOptionPane.showMessageDialog(null, errorMessage, Localisation.getMessage("error.analysisexists"), JOptionPane.WARNING_MESSAGE);
            }
            c.close();
            return toReturn;
        }
        catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }

    private String buildQuery(StringBuilder analysis, String phase) {
        String toReturn = StringQueries.PHASE_START;
        if (analysis.length() == 0) {
            return "";
        }
        String[] analysisArray = analysis.toString().split(",");
        for (int i = 0; i < analysisArray.length - 1; i++) {
            if (i == 0) {
                toReturn = toReturn + " " + String.format(StringQueries.PHASES_APPENDIX, " and ", analysisArray[i].trim(), "'" + phase + "'");
            } else {
                toReturn = toReturn + " " + String.format(StringQueries.PHASES_APPENDIX, " or ", analysisArray[i].trim(), "'" + phase + "'");
            }
        }
        return toReturn;
    }

    public ViewVersion getVersion(int analysisId) throws Exception {
        PreparedStatement ps;
        ResultSet rs;
        ViewVersion toReturn = new ViewVersion();
        try {
            Connection c = DbUtils.getDBConnectionGeneral();
            ps = c.prepareStatement(StoredQueries.getQuery(StoredQueries.VERSION_SELECTION));
            ps.setInt(1, analysisId);
            rs = ps.executeQuery();
            while (rs.next()) {
                toReturn.setVersionId(rs.getString("version_id"));
                toReturn.setNameEn(rs.getString("name_en"));
                toReturn.setNameRu(rs.getString("name_ru"));
                toReturn.setNameKz(rs.getString("name_kk"));
            }
            c.close();
            return toReturn;
        }
        catch (Exception e) {
            e.printStackTrace();
            throw e;
        }
    }

    public boolean saveData(Vector<ViewAnalysisParameter> resultParameters, ViewAnalysis va, ViewVersion vv) {
        PreparedStatement ps;
        try {
            Connection con = DbUtils.getDBConnectionGeneral();
            ps = con.prepareStatement("INSERT INTO test_parametrs_units" +
                    " (parameter_id, unit, version, analysis_instr_invest_list_id, min_value, max_value, analysis_version_key, order_of_param) " +
                    " VALUES(?, ?, ?, ?, ?, ?, ?, ?)");
            String analysisVersionKey = getAnalysisVersionKey(va, vv);
            for (ViewAnalysisParameter res : resultParameters) {
                ps.setInt(1, res.getParameterID());
                ps.setString(2, res.getUnit().getUnitKey());
                ps.setString(3, vv.getVersionId());
                ps.setInt(4, va.getAnalysisId());
                if (res.isMinAndMaxNull()) {
                    ps.setNull(5, Types.NULL);
                    ps.setNull(6, Types.NULL);
                } else {
                    ps.setDouble(5, res.getMinValue());
                    ps.setDouble(6, res.getMaxValue());

                }
                ps.setString(7, analysisVersionKey);
                ps.setInt(8, res.getOrder());
                ps.addBatch();
            }
            int[] rows = ps.executeBatch();
            if (rows.length > 0) {
                JOptionPane.showMessageDialog(null, Localisation.getMessage("info.dataadded") + " " + rows.length + " " + Localisation.getMessage("info.rows")
                        , Localisation.getMessage("info.title")
                        , JOptionPane.INFORMATION_MESSAGE);
                return true;
            }
            if (rows.length == 0) {
                JOptionPane.showMessageDialog(null, Localisation.getMessage("error.dataerror"), Localisation.getMessage("error.title"), JOptionPane.WARNING_MESSAGE);
                return false;
            }
            con.close();
            return false;
        }
        catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }

    public int getAmountOfVersions(ViewAnalysis analysis) {
        int toReturn = 0;
        try {
            Connection c = DbUtils.getDBConnectionGeneral();
            PreparedStatement ps = c.prepareStatement(StoredQueries.getQuery(StoredQueries.EXISTS_VERSION_RETRIVE));
            ps.setInt(1, analysis.getAnalysisId());
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                toReturn++;
            }
//            c.close();
            return toReturn;
        }
        catch (Exception e) {
            return 0;
        }
    }

    public Vector<ViewVersion> getVersions(ViewAnalysis analysis) {
        Vector<ViewVersion> versionsList = new Vector<ViewVersion>();
        try {

            Connection c = DbUtils.getDBConnectionGeneral();
            PreparedStatement ps = c.prepareStatement(StoredQueries.getQuery(StoredQueries.EXISTS_VERSION_RETRIVE));
            ps.setInt(1, analysis.getAnalysisId());
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                ViewVersion vv = new ViewVersion();
                vv.setVersionId(rs.getString("version_id"));
                vv.setNameEn(rs.getString("name_en"));
                vv.setNameRu(rs.getString("name_ru"));
                vv.setNameKz(rs.getString("name_kk"));
                versionsList.add(vv);
            }
            c.close();
            return versionsList;
        }
        catch (Exception e) {
            return null;
        }
    }

    private String getAnalysisVersionKey(ViewAnalysis va, ViewVersion vv) {
        StringBuilder toReturn = new StringBuilder();
        toReturn.append("A_");
        if (String.valueOf(va.getAnalysisId()).length() == 1) {
            toReturn.append("00");
        }
        if (String.valueOf(va.getAnalysisId()).length() == 2) {
            toReturn.append("0");
        }
        toReturn.append(va.getAnalysisId());
        toReturn.append("_");
        toReturn.append(vv.getVersionId());
        return toReturn.toString();
    }

}
